package com.group.member;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;

import javax.naming.Context;
import javax.naming.InitialContext;
import javax.sql.DataSource;

import com.group.member.MemberBean;

import com.group.login.ZipcodeBean;

import com.group.util.JdbcUtil;


public class MemberDAO {

	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;
	DataSource ds = null;
	
	public MemberDAO() {
		try {
			//	pooling 처리 : dbcp
			Context context = new InitialContext();
			ds = (DataSource)context.lookup("java:comp/env/jdbc/Oracle10g");
		} catch (Exception e) {
			System.out.println("연결 오류 : " + e.getMessage());
			return;
		}
	}
	
	public boolean loginChk(String id, String passwd){
		boolean result = false;
		String sql = "SELECT COUNT(*) FROM MEMBER WHERE id='"+id+"' AND passwd='"+passwd+"'";
		try {
			conn = ds.getConnection();
			pstmt = conn.prepareStatement(sql);
			rs = pstmt.executeQuery();
			if(rs.next()){
				int cnt = rs.getInt(1);
				if(cnt > 0){
					result = true;
				}
			}
		} catch (Exception e) {
			System.out.println("loginChk 오류 : " + e.getMessage());
		}finally{
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
		return result;
	}
	
	public ArrayList<ZipcodeBean> zipcodeRead(String area3){
		ArrayList<ZipcodeBean> list = new ArrayList<ZipcodeBean>();
		String sql = "select * from ziptab where area3 like ?";
		try{ 
			conn = ds.getConnection();  //db연결
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, area3 + "%");
			rs = pstmt.executeQuery();
			while(rs.next()){
				ZipcodeBean bean = new ZipcodeBean();
				bean.setZipcode(rs.getString("zipcode"));
				bean.setArea1(rs.getString("area1"));
				bean.setArea2(rs.getString("area2"));
				bean.setArea3(rs.getString("area3"));
				bean.setArea4(rs.getString("area4"));
				list.add(bean);
			}
		}catch(Exception e){
			System.out.println("zipcodeRead 오류:" + e.getMessage());
		}finally{
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
		return list;
	}
	
	public boolean checkId(String id){
		boolean b = false;
		String sql = "select id from member where id=?";
		try{
			conn = ds.getConnection();  //db연결
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, id);
			rs = pstmt.executeQuery();
			b=rs.next();
			
		}catch(Exception e){
			System.out.println("checkId 오류:" + e.getMessage());
		}finally{
			JdbcUtil.close(rs);
			JdbcUtil.close(pstmt);
			JdbcUtil.close(conn);
		}
		return b;
	}
	
	public boolean memberInsert(MemberBean bean){
		boolean b = false;
		String sql = "insert into member values(?,?,?,?,?,?,?,?,?,?,?,?)";
		try{
			conn = ds.getConnection();  //db연결
			pstmt = conn.prepareStatement(sql);
			pstmt.setString(1, bean.getId());
			pstmt.setString(2, bean.getPasswd());
			pstmt.setString(3, bean.getName());
			pstmt.setString(4, bean.getMem_num1());
			pstmt.setString(5, bean.getMem_num2());
			pstmt.setString(6, bean.getEmail());
			pstmt.setString(7, bean.getPhone());
			pstmt.setString(8, bean.getZipcode());
			pstmt.setString(9, bean.getAddress());
			pstmt.setString(10, bean.getJob());
			pstmt.setString(11, bean.getTeamGrant());
			pstmt.setString(12, bean.getDeptName());
			int count = pstmt.executeUpdate(); //성공하면 1 실패하면0을 갖음
			if(count == 1)b=true;
			
		}catch(Exception e){
			System.out.println("memberInsert 오류:" + e.getMessage());
		}finally{
			try{
			if(rs != null)rs.close();
			if(pstmt != null)pstmt.close();
			if(conn != null)conn.close();
			}catch(Exception e2){
				
			}
		}
		return b;
	}
}
